package GameServer;

import Library.ErrorCodes;
import Library.Login.*;
import java.io.*;
import java.sql.*;

/**
 * Class database maakt verbinding met de database en biedt methodes om data te
 * plaatsen, te verwijderen en aan te passen.
 */
public class Database
{
    /**
     *
     */
    protected Connection m_Connection;
    
    /**
     * maakt verbinding met een bestaande database op een lokale schijf
     * @param connectionString 
     * @param username
     * @param password
     * @throws SQLException
     * @throws IOException
     */
    public Database(String connectionString, String username, String password) throws SQLException, IOException
    {
        try
        {
            //Class.forName("interbase.interclient.Driver");
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            m_Connection = DriverManager.getConnection(connectionString, username, password);
        }
        catch (Exception ex)
        {
                ex.printStackTrace();
        }
    }  

    /**
     * ruimt het geheel op na gebruik
     */
    public void dispose()
    {
        if (m_Connection != null)
        {
            try
            {
                if (!m_Connection.isClosed())
                {
                    m_Connection.close();
                    m_Connection = null;
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
    }

    /**
     * Controleert het token van een client.
     * @param ticket
     * @return geeft aan of het token klopt of niet
     */
    public int checkToken(ITicket ticket)
    {
        int result = ErrorCodes.ERROR_TICKET_MISSING;
        Statement stmt = null;
        try
        {
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "SELECT TOKEN FROM ACCOUNT WHERE USERNAME = '" + ticket.getUsername().toUpperCase() + "';";
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next())
            {
                String token = rs.getString(1);
                result = ticket.getToken().equals(token) ?
                        ErrorCodes.ERROR_NOERROR : ErrorCodes.ERROR_TICKET_EXPIRED;
            }
            stmt.close();
            //m_Connection.commit();
            //m_Connection.setAutoCommit(true);
        }
        catch (Exception exc)
        {
            result = ErrorCodes.ERROR_UNKNOWN;
            exc.printStackTrace();
            try
            {
                m_Connection.rollback();
                    m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                    ex.printStackTrace();
            }
        }

        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                {
                    stmt.close();
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        return result;
    }

    /**
     * Geeft een lijst met (Game)Servers
     * @param id
     * @return lijst met GameServers
     */
    public WorldMap getMap(int id)
    {
        WorldMap map = new WorldMap();
        Statement stmt = null;
        boolean failed = false;
        try
        {
            // Fetch Continents
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
                    String sql = "Select ID, Name, BonusValue From Continent Where MapID = " + id + ";";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next())
            {
                map.addContinent(rs.getInt("ID"), rs.getString("Name"), rs.getInt("BonusValue"));
            }
            stmt.close();
            m_Connection.commit();
            m_Connection.setAutoCommit(true);
        }
        catch (Exception exc)
        {
            failed = true;
            exc.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
            }
        }
        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                {
                    stmt.close();
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        try
        {
            // Fetch Lands/Continent
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "Select ContinentID, ID, Name From Country Where MapID = " +
                   id +
                   " Group By ContinentID, ID, Name Order By ContinentID, ID;";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next())
            {
                map.addLand(rs.getInt("ContinentID"), rs.getInt("ID"), rs.getString("Name"));
            }
            stmt.close();
            m_Connection.commit();
            m_Connection.setAutoCommit(true);
        }
        catch (Exception exc)
        {
            failed = true;
            exc.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
            }
        }

        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                {
                    stmt.close();
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        try
        {
            // Fetch Borders
            m_Connection.setAutoCommit(false);
            stmt = m_Connection.createStatement();
            String sql = "Select MapID1, ContinentID1, CountryID1, MapID2, ContinentID2, CountryID2 From CountryBorder Where MapID1 = " +
                   id + " Order By MapID1, ContinentID1, CountryID1;";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next())
            {
                Continent c1 = map.getContinentById(rs.getInt("ContinentID1"));
                Land l1 = c1.getLand(rs.getInt("CountryID1"));
                l1.addBorderLand(rs.getInt("CountryID2"));
                
                //Continent c2 = map.GetContinentById(rs.getInt("ContinentID2"));
                //Land l2 = c2.getLand(rs.getInt("LandID2"));
            }
            stmt.close();
            m_Connection.commit();
            m_Connection.setAutoCommit(true);
        }
        catch (Exception exc)
        {
            failed = true;
            exc.printStackTrace();
            try
            {
                m_Connection.rollback();
                m_Connection.setAutoCommit(true);
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
            }
        }

        if (stmt != null)
        {
            try
            {
                if (!stmt.isClosed())
                {
                    stmt.close();
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        return failed ? null : map;
    }
}